﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	成单认领查询
-- =============================================
CREATE PROCEDURE [dbo].[Proc_Order_SelectClaim]
	
	(
		@TypeId INT ,--首款尾款
		@CusName NVARCHAR(1000),--客户名称
		@StartDate VARCHAR(10),--认领时间
		@EndDate VARCHAR(10),  --认领时间                       
		@CompanyId VARCHAR(500),--单位ID
		@DepId INT, --部门ID
		@NOE_Flag INT,
		@StartIndex INT,
		@EndIndex INT,
		@SWId int,
		@PaymentTypeId int,
		@BankId int,
		@checkxj bit,
		@rblbank INT
	)
AS
BEGIN
	DECLARE @str NVARCHAR(4000),@Sqlstr NVARCHAR(4000),@str1 NVARCHAR(4000)
	set @str =''
	set @str1 =''
	
	If @TypeId <>0
		Set @str =' o.TypeId='+CONVERT(VARCHAR,@TypeId)+' And '

	If @SWId <>0
		Set @str =@str+' opr.SWId='+CONVERT(VARCHAR,@SWId)+' And '

	If @CompanyId<>''
		Set @str=@str+' opr.CompanyId in ('+@CompanyId+') And  '
	
	If @NOE_Flag<>100
		Set @str=@str+ ' o.NOE_Flag='+CONVERT(VARCHAR,@NOE_Flag)+' And '
	
	If @DepId <>0
		Set @str1=@str1+' DepId in (SELECT id FROM (
 SELECT (SELECT d.bmbh FROM	Department d WHERE id='+CONVERT(VARCHAR,@DepId)+') AS a) AS nb1,Department 
WHERE LEFT(bmbh,LEN(a))=a) and '
		
	If @PaymentTypeId=1 --现金
	BEGIN   --查现金时，还需要查询电汇中需要算在现金中的电汇账号
		If @checkxj=0
    		Set @str1=@str1+'PaymentTypeId=1 And '
		Else 
    		Set @str1=@str1+'(PaymentTypeId=1 Or BankId IN (SELECT id FROM Finance_Bank fb WHERE fb.PaymentTypeId=1 and Companyid in ('+ @CompanyId+'))) and '
	END
	Else If @PaymentTypeId=2 --银行
	BEGIN
		If  @BankId=0 --电汇账号选择为全部时，查询所有算在银行中的电汇金额
			Set @str1=@str1+'BankId IN (SELECT id FROM Finance_Bank fb WHERE fb.PaymentTypeId='+CONVERT(VARCHAR, @rblbank)+' and Companyid in ('+ @CompanyId+')) and '
		ELSE	--选择了电汇账号时，查询该账号下的金额
			Set @str1=@str1+'BankId = (select id from Finance_Bank where id='+CONVERT(VARCHAR, @BankId)+' and  PaymentTypeId='+CONVERT(VARCHAR, @rblbank)+') and '
	END
	Else If @PaymentTypeId=3 --支票
		Set @str1=@str1+'(PaymentTypeId=3 ) And '

	If @CusName<>''
		Set @str1=@str1+' cusname Like ''%'+@CusName+'%'' And '

	If @str<>''
		Set @str=' And '+@str	--SUBSTRING(@str,1,LEN(@str)-3)

	If @str1 <>''
		Set @str1=' Where '+SUBSTRING(@str1,1,LEN(@str1)-3)

	Set @Sqlstr='Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime, ''' + @StartDate + ' 00:00:00'')
	Set @eRq = Convert(Datetime, ''' + @EndDate + ' 23:59:59'')

	;With list as
	 (Select opr.Id,
		opr.GId,opr.RId,opr.OId,opr.CusId,opr.CusName,
		opr.SalesmanAccount,opr.Salesman,opr.CreateDate,opr.Prices,
		opr.PaymentTypeId,opr.PaymentType,opr.BankId,opr.Bank,
		opr.SWId,opr.SWName,opr.shjbh,opr.FinanceFlag,opr.FinanceDate,
		opr.CompanyId,opr.[Description],
		o.NOE_Flag, o.IsGreen, o.DepId,o.YingShouPrice,
		(Case NOE_Flag When 1 Then ''[非企]'' When 2 Then ''[非常规]'' Else '''' End) As NOE_Flag_Name,
		o.TypeName,  StateId
		--  IsNull((Select Title From Order_State Where Id=o.StateId),''---'') As StateName
		--,IsNull((Select CompanyName From Company Where Id=opr.CompanyId),''---'') As CompanyName
		FROM Order_PaymentRecord opr,[Order] o 
		Where o.oid=opr.Oid	' + @str + '
		FinanceDate Between @sRq And @eRq
	 ),
	 list2 as (
		Select ROW_NUMBER() OVER (ORDER BY FinanceDate desc)AS Row,
		* From list ' + @str1 + '
	 )
	Select *,(Select count(1) from list2) as cid,(select sum(Prices) from list2) as zje  
	,IsNull((Select Title From Order_State Where Id=stateId),''---'') As StateName   
	,IsNull((Select CompanyName From Company Where Id=CompanyId),''---'') As CompanyName          
	From list2 where Row Between '+Convert(varchar,@StartIndex)+' and '+Convert(varchar,@EndIndex)+'   
	Order By row'

--PRINT(@Sqlstr)
	Exec(@Sqlstr)	
END